﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Reflection;

namespace myCollection
{
    class SQLite
    {
        private string dbConnection = string.Format("Data Source={0};", Properties.Settings.Default.db);
        private bool open = false;
        private SQLiteConnection cnn;
        private SQLiteCommand mycommand;
        private SQLiteDataReader reader;

        public void Connect()
        {
            try
            {
                this.cnn = new SQLiteConnection(dbConnection);
                this.cnn.Open();
                this.open = true;
            }
            catch (Exception e)
            {
                this.open = false;
                throw new Exception(e.Message);
            }
        }

        public void Connect(string file)
        {
            try
            {
                string filedb = string.Format("Data Source={0};", file);
                this.cnn = new SQLiteConnection(filedb);
                if (System.IO.File.Exists(file) == false)
                {
                    this.cnn.CreateCommand();
                }
                this.cnn.Open();
                this.open = true;
            }
            catch (Exception e)
            {
                this.open = false;
                throw new Exception(e.Message);
            }
        }

        public void Disconnect()
        {
            this.cnn.Close();
        }

        public DataTable Select(string sql, string[,] param = null)
        {
            DataTable dt = new DataTable();
            if (this.open)
            {
                this.mycommand = new SQLiteCommand(cnn);
                this.mycommand.CommandType = CommandType.Text;
                this.mycommand.Parameters.Clear();
                if (param != null)
                {
                    for (int i = 0; i < (param.Length / 2); i++)
                    {
                        this.mycommand.Parameters.Add(new SQLiteParameter(param[i, 0].ToString(), param[i, 1]));
                    }
                }
                this.mycommand.CommandText = sql;
                this.reader = this.mycommand.ExecuteReader();
                dt.Load(reader);
                this.reader.Close();
            }
            return dt;
        }

        public int Count(string sql, string[,] param = null)
        {
            object i0 = "";
            if (this.open)
            {
                this.mycommand = new SQLiteCommand(cnn);
                this.mycommand.Parameters.Clear();
                if (param != null)
                {
                    for (int i = 0; i < (param.Length / 2); i++)
                    {
                        this.mycommand.Parameters.Add(new SQLiteParameter(param[i, 0].ToString(), param[i, 1]));
                    }
                }
                this.mycommand.CommandText = sql;
                i0 = this.mycommand.ExecuteScalar();
            }
            return int.Parse(i0.ToString());
        }

        public bool NonSelect(string sql, string[,] param = null)
        {
            bool b = false;
            if (this.open)
            {
                try
                {
                    this.mycommand = new SQLiteCommand(cnn);
                    this.mycommand.CommandType = CommandType.Text;
                    this.mycommand.Parameters.Clear();
                    if (param != null)
                    {
                        for (int i = 0; i < (param.Length / 2); i++)
                        {
                            this.mycommand.Parameters.Add(new SQLiteParameter(param[i, 0].ToString(), param[i, 1]));
                        }
                    }
                    this.mycommand.CommandText = sql;
                    this.mycommand.ExecuteNonQuery();
                    b = true;
                }
                catch
                {
                    b = false;
                }
            }
            return b;
        }

        public int LastInsertId()
        {
            return int.Parse(this.Select("SELECT last_insert_rowid();").Rows[0][0].ToString());
        }

        public string GetVersionDB()
        {
            return this.Select("SELECT value FROM options WHERE name = 'dbversion';").Rows[0][0].ToString();
        }

        public void InitDB()
        {
            this.NonSelect(Properties.Resources.initdb);
        }

        public bool ExistDictionary(Dictionary.TypeD type, string name)
        {
            bool b = true;
            int i = this.Count("SELECT COUNT(*) FROM " + type.ToString().ToLower() + " WHERE name = @name;", new string[1,2] { { "@name", name } });
            if (i == 0)
            {
                b = false;
            }
            return b;
        }

        public bool ExistDictionaryUpdate(Dictionary.TypeD type, string name)
        {
            bool b = true;
            int i = this.Count("SELECT COUNT(*) FROM " + type.ToString().ToLower() + " WHERE name = @name;", new string[1, 2] { { "@name", name } });
            if (i < 2)
            {
                b = false;
            }
            return b;
        }

        public bool AddDictionary(Dictionary.TypeD type, string name)
        {
            bool b = false;
            if (this.ExistDictionary(type, name) == false)
            {
                b = this.NonSelect("INSERT INTO " + type.ToString().ToLower() + " (name) VALUES (@name);", new string[1, 2] { { "@name", name } });
            }
            return b;
        }

        public bool UpdateDictionary(Dictionary.TypeD type, string name, int id)
        {
            bool b = false;
            if (this.ExistDictionaryUpdate(type, name) == false)
            {
                b = this.NonSelect("UPDATE " + type.ToString().ToLower() + " SET name = @name WHERE id = @id;", new string[2, 2] { { "@name", name }, { "@id", id.ToString() } });
            }
            return b;
        }

        public DataTable GetDictionary(Dictionary.TypeD type, string name)
        {
            return this.Select("SELECT id, name, (SELECT COUNT(*) FROM records r WHERE r." + type.ToString().ToLower() + "_id" + " = d.id) AS count FROM " + type.ToString().ToLower() + " d WHERE name LIKE @name ORDER BY name;", new string[1, 2] { { "@name", "%" + name + "%" } });
        }

        public DataTable GetDictionary(Dictionary.TypeD type, int id)
        {
            return this.Select("SELECT * FROM " + type.ToString().ToLower() + " WHERE id = @id;", new string[1, 2] { { "@id", id.ToString() } });
        }

        public bool DeleteDictionary(Dictionary.TypeD type, int id)
        {
            return this.NonSelect("DELETE FROM " + type.ToString().ToLower() + " WHERE id = @id;", new string[1, 2] { { "@id", id.ToString() } });
        }

        public bool ExistRecord(string name)
        {
            bool b = true;
            int i = this.Count("SELECT COUNT(*) FROM records WHERE name = @name;", new string[1, 2] { { "@name", name } });
            if (i == 0)
            {
                b = false;
            }
            return b;
        }

        public bool ExistRecordUpdate(string name)
        {
            bool b = true;
            int i = this.Count("SELECT COUNT(*) FROM records WHERE name = @name;", new string[1, 2] { { "@name", name } });
            if (i < 2)
            {
                b = false;
            }
            return b;
        }

        public bool AddRecord(string name, int mediatype_id, int datatype_id, int category_id, int place_id, string info)
        {
            bool b = false;
            if (this.ExistRecord(name) == false)
            {
                b = this.NonSelect("INSERT INTO records (name, mediatype_id, datatype_id, category_id, place_id, info) VALUES (@name, @mediatype, @datatype, @category, @place, @info);", new string[6, 2] { { "@name", name } , { "@mediatype", mediatype_id.ToString()}, {"@datatype", datatype_id.ToString()}, {"@category", category_id.ToString()}, {"@place", place_id.ToString()}, {"@info", info}});
            }
            return b;
        }

        public bool UpdateRecord(string name, int mediatype_id, int datatype_id, int category_id, int place_id, string info, int id)
        {
            bool b = false;
            if (this.ExistRecordUpdate(name) == false)
            {
                b = this.NonSelect("UPDATE records SET name = @name, mediatype_id = @mediatype, datatype_id = @datatype, category_id = @category, place_id = @place, info = @info WHERE id = @id;", new string[7, 2] { { "@name", name }, { "@mediatype", mediatype_id.ToString() }, { "@datatype", datatype_id.ToString() }, { "@category", category_id.ToString() }, { "@place", place_id.ToString() }, { "@info", info }, {"@id", id.ToString()} });
            }
            return b;
        }

        public DataTable GetRecords(string name)
        {
            return this.Select("SELECT id, name, (SELECT name FROM mediatype m WHERE m.id = r.mediatype_id) AS mediatype, (SELECT name FROM datatype d WHERE d.id = r.datatype_id) AS datatype, (SELECT name FROM category c WHERE c.id = r.category_id) AS category, (SELECT name FROM place p WHERE p.id = r.place_id) AS place FROM records r WHERE name LIKE @name ORDER BY name;", new string[1, 2] { { "@name", "%" + name + "%"} });
        }

        public DataTable GetRecordsInfo(string info)
        {
            return this.Select("SELECT id, name, (SELECT name FROM mediatype m WHERE m.id = r.mediatype_id) AS mediatype, (SELECT name FROM datatype d WHERE d.id = r.datatype_id) AS datatype, (SELECT name FROM category c WHERE c.id = r.category_id) AS category, (SELECT name FROM place p WHERE p.id = r.place_id) AS place FROM records r WHERE info LIKE @info ORDER BY name;", new string[1, 2] { { "@info", "%" + info + "%"} });
        }

        public DataTable GetRecords(string name, Dictionary.TypeD type0, int dictionaryId)
        {
            return this.Select("SELECT id, name, (SELECT name FROM mediatype m WHERE m.id = r.mediatype_id) AS mediatype, (SELECT name FROM datatype d WHERE d.id = r.datatype_id) AS datatype, (SELECT name FROM category c WHERE c.id = r.category_id) AS category, (SELECT name FROM place p WHERE p.id = r.place_id) AS place FROM records r WHERE name LIKE @name AND " + type0.ToString().ToLower() + "_id = @id ORDER BY name;", new string[2, 2] { { "@name", "%" + name + "%" }, { "@id", dictionaryId.ToString() } });
        }

        public DataTable GetRecord(int id)
        {
            return this.Select("SELECT * FROM records WHERE id = @id;", new string[1, 2] { { "@id", id.ToString() } });
        }

        public bool DeleteRecord(int id)
        {
            return this.NonSelect("DELETE FROM records WHERE id = @id;", new string[1, 2] { { "@id", id.ToString() } });
        }

        public DataTable GetRecordFiles(int recordId)
        {
            return this.Select("SELECT id, file FROM files WHERE record_id = @id ORDER BY file;", new string[1, 2] { { "@id", recordId.ToString() } });
        }

        public DataTable GetAllFiles(string file = "")
        {
            return this.Select("SELECT f.id, f.file, r.id, r.name FROM files f LEFT JOIN records r ON f.record_id = r.id WHERE f.file LIKE @file ORDER BY r.name;", new string[1, 2] { { "@file", "%" + file + "%"} });
        }

        public bool AddFile(string fileName, int recordId)
        {
            return this.NonSelect("INSERT INTO files (file, record_id) VALUES (@file, @id);", new string[2, 2] { { "@file", fileName }, {"@id", recordId.ToString()}});
        }

        public bool DeleteFile(string fileName)
        {
            return this.NonSelect("DELETE FROM files WHERE file = @file;", new string[1, 2] { { "@file", fileName }});
        }

        public bool DeleteRecordFiles(int recordId)
        {
            return this.NonSelect("DELETE FROM files WHERE record_id = @id';", new string[1, 2] { { "@id", recordId.ToString() } });
        }

        public bool AddHistory(string msg, int recordId)
        {
            return this.NonSelect("INSERT INTO history (record_id, text) VALUES (@id, @msg);", new string[2, 2] { { "@id", recordId.ToString() }, {"@msg", msg} });
        }

        public DataTable GetRecordHisory(int recordId)
        {
            return this.Select("SELECT text, date FROM history WHERE record_id = @id ORDER BY date;", new string[1, 2] { { "@id", recordId.ToString() } });
        }
    }
}
